package com.db.jdbc;

import com.bean.Admin;
import com.bean.PaginationSupport;
import com.bean.Post;
import com.bean.User;
import com.db.PostRepository;
import com.db.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class JdbcPostRepository implements PostRepository {


    private JdbcTemplate jdbc;

    @Autowired
    private JdbcUserRepository userRepository;

    @Autowired
    public JdbcPostRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    private static final String SELECT_POST = "select post.postID,post.topic,post.content,user.userID,user.userName,postedTime,clickCount,state from post,user " +
                                                " where user.userID = post.poster and state != 0 ";

    private static final String SELECT_PAGE_POST = SELECT_POST + "order by state desc , postedTime desc limit ? offset ? ";

    private static final String SELECT_POST_POSTID = SELECT_POST + " and postID = ? " ;

    private static final String SELECT_PAGE_POST_POSTER = SELECT_POST + " and poster = ? order by state desc , postedTime desc limit ? offset ? ";

    private static final String SELECT_PAGE_POST_KEYWORD_PREFIX = SELECT_POST + " and topic like '%";

    private static final String SELECT_PAGE_POST_KEYWORD_SUFFIX = "%' order by state desc , postedTime desc limit ? offset ? ";

    private static final String COUNT_POSTID = "select count(postID) from post ";

    private static final String UPDATE_POST = "update post set ";

    private static final String UPDATE_POST_TOP = UPDATE_POST + " state = ? where postID = ? ";

    private static class postRowMapper implements RowMapper<Post> {

        //@Autowired
        //private JdbcCommentRepository commentRepository;

        public Post mapRow(ResultSet rs, int rowNum) throws SQLException {

            Post post = new Post(rs.getInt("post.postID"), rs.getString("post.topic"),rs.getString("post.content")
                    ,new User(rs.getInt("user.userID"),rs.getString("user.userName")), rs.getTimestamp("post.postedTime")
                    ,rs.getInt("post.clickCount"),rs.getInt("post.state"));


            //post.setCommentList( commentRepository.findByPost(post));

            return post;
        }
    }

    private int insertPostandReturnID(Post post){
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbc).withTableName("post");
        jdbcInsert.setGeneratedKeyName("postID");
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("topic", post.getTopic());
        args.put("content",post.getContent());
        args.put("poster", post.getPoster().getUserID());
        args.put("postedTime", post.getPostedTime());
        args.put("clickCount" , post.getClickCount());
        args.put("commentList" , post.getCommentList());
        args.put("state" , post.getState());
        int postID = jdbcInsert.executeAndReturnKey(args).intValue();
        return postID;
    }

    @Override
    public Post save(Post post) {
        int postID = insertPostandReturnID(post);
        return new Post(postID,post.getTopic(),post.getContent(),post.getPoster(),post.getPostedTime(),post.getClickCount(),null,post.getState());
    }

    @Override
    public long countTop() {
        return jdbc.queryForLong(COUNT_POSTID +" where state = 2" );
    }

    @Override
    public long countPost() {
        return jdbc.queryForLong(COUNT_POSTID + " where state != 0 ");
    }

    @Override
    public long countKeyword(String keyword) {
            return jdbc.queryForLong(COUNT_POSTID + "where topic like '%" + keyword + "%\' and state != 0 ");
    }

    @Override
    public long countMyPost(User poster) {
        return jdbc.queryForLong(COUNT_POSTID+ " where poster = ? and state != 0 " , poster.getUserID() );
    }

    @Override
    public PaginationSupport<Post> findAllPostPage(int pageNo, int pageSize) {
        int totalCount = (int) countPost();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Post>(new ArrayList<Post>(0), 0);

        List<Post> items = jdbc.query(SELECT_PAGE_POST, new postRowMapper(), pageSize, startIndex);

        PaginationSupport<Post> ps = new PaginationSupport<Post>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    @Override
    public PaginationSupport<Post> findMyPostPage(User poster, int pageNo, int pageSize) {
        int totalCount = (int) countMyPost(poster);
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Post>(new ArrayList<Post>(0), 0);

        List<Post> items = jdbc.query(SELECT_PAGE_POST_POSTER , new postRowMapper() , poster.getUserID(), pageSize, startIndex);

        PaginationSupport<Post> ps = new PaginationSupport<Post>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    @Override
    public Post setTop(Post post) {
        try{
            if(post.getState() == 1){
                jdbc.update(UPDATE_POST_TOP , 2,post.getPostID());
                post.setState(2);
            }else if(post.getState() == 2){
                jdbc.update(UPDATE_POST_TOP, 1, post.getPostID());
                post.setState(1);
            }
        }catch(Exception e){
        }
        return post;
    }


    @Override
    public PaginationSupport<Post> findByKeyWord(String keyword, int pageNo, int pageSize) {
        int totalCount = (int) countKeyword(keyword);
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Post>(new ArrayList<Post>(0), 0);

        List<Post> items = jdbc.query(SELECT_PAGE_POST_KEYWORD_PREFIX + keyword + SELECT_PAGE_POST_KEYWORD_SUFFIX , new postRowMapper() , pageSize, startIndex);

        PaginationSupport<Post> ps = new PaginationSupport<Post>(items, totalCount, pageSize, startIndex);
        return ps;

    }

    @Override
    public Post findOne(int postID) {
        Post post = jdbc.queryForObject(SELECT_POST_POSTID, new postRowMapper(), postID);
        return post;
    }

    @Override
    public Post modifyMyPost(Post post, User poster,String newTopic , String newContent) {

        if(post.getPoster().getUserID() == poster.getUserID()){
            try{
                jdbc.update(UPDATE_POST + " topic = ? , content = ? where postID = ? ",newTopic,newContent,post.getPostID());
                post.setTopic(newTopic);
                post.setContent(newContent);
            }catch(Exception e){
            }
            return post;
        }
        return post;
    }

    @Override
    public Post adminModifyPost(Post post, Admin admin, String newTopic, String newContent) {
            try{
                jdbc.update(UPDATE_POST + " topic = ? , content = ? where postID = ? ",newTopic,newContent,post.getPostID());
                post.setTopic(newTopic);
                post.setContent(newContent);
            }catch(Exception e){
            }
            return post;
    }

    @Override
    public void deletePost(Post post) {
        jdbc.update(UPDATE_POST + " state = 0 where postID = ? ",post.getPostID());
    }

    @Override
    public void clickPost(Post post) {

        jdbc.update(UPDATE_POST + " clickCount = ? where postID = ? ",post.getClickCount(),post.getPostID());

    }
}
